package com.kaishengit.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.kaishengit.entity.Idea;
import com.kaishengit.entity.Project;
import com.kaishengit.entity.User;
import com.kaishengit.util.DBHelp;
import com.kaishengit.util.RowMapper;

public class IdeaDao {
	
	private DBHelp<Idea>   db=new DBHelp<Idea>();
	/*
	 * SELECT ti.id,ti.title,ti.content,ti.createtime,ti.t_project_id, tp.name AS tpname ,tp.desc ,tp.createtime,ti.t_user_id,tu.name ,tu.password,tu.pic FROM t_idea AS ti
LEFT JOIN t_user AS tu ON  ti.t_user_id=tu.id
LEFT JOIN t_project AS tp ON ti.t_project_id=tp.id
WHERE ti.t_project_id='p1';
	 * 
	 * */
	public void del (String id){
		final String sql=" DELETE FROM t_idea WHERE id=?  ";
		db.executeSQL(sql, id);
		
	}
	
	public void save(Idea idea){
		final  String sql="INSERT INTO t_idea(id,title,content,createtime,t_project_id,t_user_id ) VALUES(?,?,?,?,?,?) ";
		db.executeSQL(sql, idea.getId(),idea.getTitle(),idea.getContent(),idea.getCreatetime(),idea.getProjectId(),idea.getUserId());	
	}
	 public List<Idea> findByProjectId(String ProjectId){
		 StringBuilder sql=new StringBuilder();
		 sql.append(" SELECT ti.id,ti.title,ti.content,ti.createtime,ti.t_project_id, tp.name AS tpname ,tp.desc ,tp.createtime AS tpcreatetime,ti.t_user_id,tu.name ,tu.password,tu.pic ");
		 sql.append(" FROM t_idea AS ti ");
		 sql.append(" LEFT JOIN t_user AS tu ON  ti.t_user_id=tu.id ");
		 sql.append(" LEFT JOIN t_project AS tp ON ti.t_project_id=tp.id ");
		 sql.append(" WHERE ti.t_project_id= ? ");
		 
		 
		 return db.executeQueryForList(sql.toString(), new IdeaRowMapper(), ProjectId);
	 }
	 
	 public Idea findByIdeaId(String IdeaId){
		 
		 StringBuilder sql=new StringBuilder();
		 sql.append(" SELECT ti.id,ti.title,ti.content,ti.createtime,ti.t_project_id, tp.name AS tpname ,tp.desc ,tp.createtime AS tpcreatetime,ti.t_user_id,tu.name ,tu.password,tu.pic ");
		 sql.append(" FROM t_idea AS ti ");
		 sql.append(" LEFT JOIN t_user AS tu ON  ti.t_user_id=tu.id ");
		 sql.append(" LEFT JOIN t_project AS tp ON ti.t_project_id=tp.id ");
		 sql.append(" WHERE ti.id= ? ");
		return db.executeQueryForObject(sql.toString(), new IdeaRowMapper(),IdeaId);
		 
		 
	 }
	 
	 public class IdeaRowMapper implements RowMapper<Idea> {

			public Idea mapperRow(ResultSet rs) throws SQLException {
				
				Idea idea=new Idea();
				idea.setId(rs.getString("id"));
				idea.setTitle(rs.getString("title"));
				idea.setContent(rs.getString("content"));
				idea.setCreatetime(rs.getString("createtime"));
				
				User user=new User();
				user.setId(rs.getString("t_user_id"));
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setPic(rs.getString("pic"));
				
				idea.setUser(user);
				idea.setUserId(rs.getString("t_user_id"));
				
				Project p=new Project();
				p.setId(rs.getString("t_project_id"));
				p.setName(rs.getString("tpname"));
				p.setDesc(rs.getString("desc"));
				p.setCreatetime(rs.getString("tpcreatetime"));
				
				idea.setProject(p);
				idea.setProjectId(rs.getString("t_project_id"));				
				
				return idea;
			}

		}
}
